package com.portal.dao;

import java.util.Calendar;
import java.util.List;

import javax.ejb.Stateless;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;

@Stateless
public class RelatorioProdutividadeDAO {
	private final static String UNIT_NAME = "CAR10";

	@PersistenceContext(unitName = UNIT_NAME)
	private EntityManager em;

	@SuppressWarnings("unchecked")
	public List<Object[]> generateRelatorioProdutividade(Calendar dataInicial, Calendar dataFinal) {
		Query query = em.createNativeQuery("SELECT u.nom AS USUARIO, DATA, count(cd_usuariocriacao) AS REGISTROS FROM ("
				+ "SELECT id_contrato,cd_usuariocriacao, to_char(dt_criacao) AS DATA "
				+ "FROM val_contrato_validado "
				+ "WHERE dt_criacao BETWEEN :dataInicial AND :dataFinal "
				+ "GROUP BY id_contrato,cd_usuariocriacao, to_char(dt_criacao)) UR "
				+ "INNER JOIN vw_val_usuarios u on UR.cd_usuariocriacao = u.sequsu "
				+ "GROUP BY u.nom, DATA "
				+ "ORDER BY u.nom, DATA");
		query.setParameter("dataInicial", dataInicial);
		query.setParameter("dataFinal", dataFinal);
		return (List<Object[]>)query.getResultList();
	}
}
